import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { IBaseEntity } from '../00_base/IBaseEntity';
import { Result } from '../../common/result/Result';

@Injectable()
export class FileService {
  constructor(
    @InjectRepository(IBaseEntity)
    private baseRepository: Repository<IBaseEntity>,
  ) {}
  async importTable(table: string, sheet: any[]): Promise<Result<any>> {
    // 1. 查询表字段信息
    const tableArgs: any[] = await this.baseRepository
      .query(`SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '${table}';
`);
    if (tableArgs.length == 0) {
      return Result.failed(new Result<any>('F0101', '表不存在'));
    }
    // 2. 校验表字段信息与表内容是否一致,只提取表头里携带的信息
    // 3. 表格信息第二行为中文注释信息，表头是第一行，应与数据库字段一一对应
    const sheetArgs = sheet[0];
    const sheetVals = sheet.slice(2);
    let argSQL = ``;
    const valSQLList = [];
    for (let i = 0; i < sheetArgs.length; i++) {
      for (const tableArg of tableArgs) {
        if (tableArg.COLUMN_NAME == sheetArgs[i]) {
          argSQL += `[${sheetArgs[i]}],`;
          for (let j = 0; j < sheetVals.length; j++) {
            let val = sheetVals[j][i];
            if (!val) {
              val = null;
            } else {
              switch (tableArg.DATA_TYPE) {
                case 'nvarchar':
                  val = `N'${val}'`;
                  break;
                case 'varchar':
                  val = `'${val}'`;
              }
            }
            valSQLList[j] = (valSQLList[j] ? valSQLList[j] : '') + val + ',';
          }
        }
      }
    }
    argSQL = '(' + argSQL.replace(/,+$/, '') + ')';
    let valSQL = '';
    for (let i = 0; i < valSQLList.length; i++) {
      valSQLList[i] = '(' + valSQLList[i].replace(/,+$/, '') + ')';
      valSQL += valSQLList[i] + ',';
    }
    valSQL = valSQL.replace(/,+$/, '');
    const insertSQL = `INSERT INTO [dbo].[${table}] ${argSQL} VALUES ${valSQL}`;
    await this.baseRepository.query(insertSQL);
    // 3. 插入表字段信息
    return Result.success();
  }
}
